/**
 * 
 */
package tani.bnn.com.mbilling.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import tani.bnn.com.mbilling.core.Product;
import tani.bnn.com.mbilling.core.ProductPurchaseDetail;
import tani.bnn.com.mbilling.core.ProductSaleDetail;
import tani.bnn.com.mbilling.core.QuantityPriceDetail;
import tani.bnn.com.mbilling.core.SalePurchase;
import tani.bnn.com.mbilling.utility.ConnectionManager;
import tani.bnn.com.mbilling.utility.DateUtils;
import tani.bnn.com.mbilling.utility.MBillingConstant;

/**
 * @author netesh.thukral
 *
 */
public class SalePurchaseDAO {
	private Connection conn;
	private static String purchaseQry = " select 'Purchase' type, cm.name comp_name, ct.name cat_name, pd.id prod_id, pd.name prod_name, pd.model, " +
			"		pd.search_code, sum(pud.quantity) Quantity, pud.price price " +
			"	from companies cm " +
			"		inner join categories ct on cm.id = ct.company_id " +
			"		inner join products pd on cm.id = pd.company_id and ct.id = pd.category_id " +
			"	 	inner join purchase_details pud on pd.id = pud.product_id ";
	private static String purchaseGrpByQry = "\t\t group by cm.name, ct.name, pd.name, pud.price, pd.id, pd.search_code, pd.model ";
	private static String saleQry = " select 'Sale' type, cm.name comp_name, ct.name cat_name, pd.id prod_id, pd.name prod_name, pd.model, " +
			"		pd.search_code, -sum(sd.quantity) Quantity, sd.sale_price price " +
			"	from companies cm" +
			"		inner join categories ct on cm.id = ct.company_id " +
			"		inner join products pd on cm.id = pd.company_id and ct.id = pd.category_id " +
			"		inner join sale_details sd on pd.id = sd.product_id ";
	private static String saleGrpByQry = "group by cm.name, ct.name, pd.name, pd.id, sd.sale_price, pd.search_code, pd.model ";
	private static String balStockQry = "select prod_id, comp_name, cat_name, prod_name, model, search_code, sum(Quantity) Stock " +
			"from ( select cm.name comp_name, ct.name cat_name, pd.id prod_id, pd.name prod_name, pd.model," +
			"  		pd.search_code, sum(coalesce(pud.quantity,0)) Quantity" +
			" from companies cm" +
			"  		inner join categories ct on cm.id = ct.company_id" +
			"  		inner join products pd on cm.id = pd.company_id and ct.id = pd.category_id" +
			" 		left outer join purchase_details pud on pd.id = pud.product_id " +
			"		where pd.deleted = 0 " +
			"  	group by cm.name, ct.name, pd.name, pd.id, pd.search_code, pd.model  union all " +
			"select cm.name comp_name, ct.name cat_name, pd.id prod_id, pd.name prod_name, pd.model," +
			"		pd.search_code, -sum(coalesce(sd.quantity,0)) Quantity" +
			" from companies cm" +
			"		inner join categories ct on cm.id = ct.company_id" +
			"		inner join products pd on cm.id = pd.company_id and ct.id = pd.category_id" +
			"		left outer join sale_details sd on pd.id = sd.product_id" +
			"		where pd.deleted = 0 " +
			"		group by cm.name, ct.name, pd.name, pd.id, pd.search_code, pd.model ) stocks" +
			"	group by prod_id, comp_name, cat_name, prod_name, model, search_code";
	private Connection getConnection() throws Exception{
		if(conn == null){
			conn = ConnectionManager.getNewConnection();
		}
		return conn;
	}
	public List<SalePurchase> getTodaysSalePurchase(){
		return getGivenDaySalePurchase(new Date(System.currentTimeMillis()));
	}
	public List<SalePurchase> getGivenDaySalePurchase(Date givenDay){
		List<SalePurchase> salePurchaseList = new ArrayList<SalePurchase>();
		try {
			Connection conn = getConnection();
			String strSQL =  SalePurchaseDAO.purchaseQry +
					"	where date_format(pud.created_at, '%Y-%c-%e') = ? " + 
					SalePurchaseDAO.purchaseGrpByQry + " union all " + saleQry +
					"    where date_format(sd.created_at, '%Y-%c-%e') = ? " + 
					SalePurchaseDAO.saleGrpByQry + " order by prod_id, type";
			PreparedStatement ps = conn.prepareStatement(strSQL);
			ps.setDate(1, givenDay);
			ps.setDate(2, givenDay);
			ResultSet rs = ps.executeQuery();
			populateSalePurchaseList(rs, salePurchaseList);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	public List<SalePurchase> getAllSalePurchase(){
		List<SalePurchase> salePurchaseList = new ArrayList<SalePurchase>();
		try {
			Connection conn = getConnection();
			String strSQL = SalePurchaseDAO.purchaseQry + 
					"		where pd.deleted = 0 " +
					SalePurchaseDAO.purchaseGrpByQry +
					" union all " + SalePurchaseDAO.saleQry + 
					"		where pd.deleted = 0 " +
					SalePurchaseDAO.saleGrpByQry +
					" order by prod_id, type";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(strSQL);
			populateSalePurchaseList(rs, salePurchaseList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	public List<SalePurchase> getGivenMonthSalePurchase(String monthYear){
		List<SalePurchase> salePurchaseList = new ArrayList<SalePurchase>();
		Date dayBegin = null;
		Date dayEnd = null;
		try {
			dayBegin = new Date(DateUtils.getMonthStart(monthYear).getTime());
			dayEnd = new Date(DateUtils.getMonthEnd(monthYear).getTime());
			Connection conn = getConnection();
			String strSQL = SalePurchaseDAO.purchaseQry + 
					"	where date_format(pud.created_at, '%Y-%c-%e') >= ? " +
					"		and date_format(pud.created_at, '%Y-%c-%e') < ? " +
					SalePurchaseDAO.purchaseGrpByQry + " union all " +
					SalePurchaseDAO.saleQry +
					"    where date_format(sd.created_at, '%Y-%c-%e') >= ? " +
					"		and date_format(sd.created_at, '%Y-%c-%e') < ? " +
					SalePurchaseDAO.saleGrpByQry + " order by prod_id, type";
			PreparedStatement ps = conn.prepareStatement(strSQL);
			ps.setDate(1, dayBegin);
			ps.setDate(2, dayEnd);
			ps.setDate(3, dayBegin);
			ps.setDate(4, dayEnd);
			ResultSet rs = ps.executeQuery();
			populateSalePurchaseList(rs, salePurchaseList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	private void populateSalePurchaseList(ResultSet rs, List<SalePurchase> salePurchaseList) throws SQLException{
		while (rs.next()){
			SalePurchase salePurchase = new SalePurchase();
			salePurchase.setId(rs.getInt("prod_id"));
			salePurchase.setName(rs.getString("prod_name"));
			salePurchase.setCategoryName(rs.getString("cat_name"));
			salePurchase.setCompanyName(rs.getString("comp_name"));
			salePurchase.setModel(rs.getString("model"));
			salePurchase.setSearchCode(rs.getString("search_code"));
			salePurchase.setType(rs.getString("type"));
			salePurchase.setQuantity(rs.getInt("Quantity"));
			salePurchase.setPrice(rs.getInt("price"));
			salePurchaseList.add(salePurchase);
		}
		
	}
	public List<Product> getAllProductList(){
		List<Product> productList = new ArrayList<Product>();
		try {
			Connection conn = getConnection();
			String strSQL = SalePurchaseDAO.purchaseQry + SalePurchaseDAO.purchaseGrpByQry +
					" union all " + SalePurchaseDAO.saleQry + SalePurchaseDAO.saleGrpByQry +
					" order by prod_id, type, price desc";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(strSQL);
			populateProductList(rs, productList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(!productList.isEmpty()){
			for(Product product: productList){
				product.setProfitLoss();
			}
		}
		return productList;
	}
	public List<Product> getAllProductList(String beforeDate){
		List<Product> productList = new ArrayList<Product>();
		Date date = null;
		try {
			date = new Date(DateUtils.getDateValue(beforeDate, DateUtils.DATE_MONTH_FORMAT).getTime());
			Connection conn = getConnection();
			String strSQL = SalePurchaseDAO.purchaseQry + "\n	where date(pud.created_at) <  ?  \n " +
					SalePurchaseDAO.purchaseGrpByQry + "\n union all \n" + 
					SalePurchaseDAO.saleQry + "\n    where date(sd.created_at) <  ?  \n " + 
					SalePurchaseDAO.saleGrpByQry + "\n order by prod_id, type, price desc";
			PreparedStatement stmt = conn.prepareStatement(strSQL);
			stmt.setDate(1, date);
			stmt.setDate(2, date);
			stmt.setFetchSize(1000);
			ResultSet rs = stmt.executeQuery();
			populateProductList(rs, productList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(!productList.isEmpty()){
			for(Product product: productList){
				product.setProfitLoss();
			}
		}
		return productList;
	}
	private void populateProductList(ResultSet rs, List<Product> productList) throws SQLException{
		int oldProdId = 0;
		int newProdId = 0;
		Product product = null;
		while (rs.next()){
			newProdId = rs.getInt("prod_id");
			if(newProdId != oldProdId){
				oldProdId = newProdId;
				product = new Product();
				productList.add(product);
				product.setId(newProdId);
				product.setName(rs.getString("prod_name"));
				product.setCategoryName(rs.getString("cat_name"));
				product.setCompanyName(rs.getString("comp_name"));
				product.setModel(rs.getString("model"));
				product.setSearchCode(rs.getString("search_code"));
			}
			QuantityPriceDetail details = null;
			if(MBillingConstant.SALE.equals(rs.getString("type"))){
				details = new ProductSaleDetail();
				product.getSales().add((ProductSaleDetail)details);
			} else {
				details = new ProductPurchaseDetail();
				product.getPurchases().add((ProductPurchaseDetail)details);
			}
			details.setQuantity(rs.getInt("Quantity"));
			details.setPrice(rs.getInt("price"));
		}
	}
	/**
	 * null returns all available +ive stock
	 * 0 returns empty stocks
	 * -1 returns all negative stocks
	 * +ive number returns stocks >0 and <= +ive number
	 * @param maxStock
	 * @return
	 */
	public List<SalePurchase> getProductStockList(Integer maxStock){
		List<SalePurchase> salePurchaseList = new ArrayList<SalePurchase>();
		try {
			Connection conn = getConnection();
			String having = maxStock == null ? "	having sum(Quantity) > 0 ":
				maxStock == 0 ? "	having sum(Quantity) = 0 ":
					maxStock < 0 ? "	having sum(Quantity) < 0 ":
				"	having sum(Quantity) > 0 and sum(Quantity) <= " +   
						maxStock; 
			String strSQL = SalePurchaseDAO.balStockQry  +
					having +" order by prod_id";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(strSQL);
			populateStockList(rs, salePurchaseList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	private void populateStockList(ResultSet rs, List<SalePurchase> salePurchaseList) throws SQLException{
		while (rs.next()){
			SalePurchase salePurchase = new SalePurchase();
			salePurchase.setId(rs.getInt("prod_id"));
			salePurchase.setName(rs.getString("prod_name"));
			salePurchase.setCategoryName(rs.getString("cat_name"));
			salePurchase.setCompanyName(rs.getString("comp_name"));
			salePurchase.setModel(rs.getString("model"));
			salePurchase.setSearchCode(rs.getString("search_code"));
			salePurchase.setStock(rs.getInt("Stock"));
			salePurchaseList.add(salePurchase);
		}
		
	}
	@SuppressWarnings("unused")
	public static void main(String[] args){
		try{
			Date dt = new Date(DateUtils.getDateValue("18-08-2012").getTime());
			SalePurchaseDAO spDAO = new SalePurchaseDAO();
			List<SalePurchase> salePurchaseList = spDAO.getGivenDaySalePurchase(dt);
			List<SalePurchase> monthlysalePurchaseList = spDAO.getGivenMonthSalePurchase("08-2012");
			List<SalePurchase> allsalePurchaseList = spDAO.getAllSalePurchase();
		} catch (Exception ex){
			
		}
	}
}